<?php


namespace app\adminbackstage\controller;


use app\adminbackstage\model\Detailed;
use app\adminbackstage\model\Operation;
use app\adminbackstage\model\Recharge;
use app\adminbackstage\model\User_amount;
use app\adminbackstage\model\AdminSalesmanRule;
use PhpOffice\PhpSpreadsheet\Cell\DataType as PHPExcel_Cell_DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment as PHPExcel_Style_Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border as Border;
use PhpOffice\PhpSpreadsheet\Style\Fill as PHPExcel_Style_Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\facade\Config;
use think\facade\Request;

class RechargeController
{
    //充值记录
    public function index(){
        $page = Request::param('page');
        $num = Request::param('num');
        $phone = Request::param('phone');//商家手机号码
        $type = Request::param('type');//支付类型 1：银行卡  2：支付宝  3：微信  4：杭州银行自助充值 ，5：汇付天下支付宝扫码   6：汇付天下微信公众号充值
        $state =  Request::param('state'); //状态  0全部   1待审/待支付  2审核通过/充值成功  3审核失败/充值失败
        $startTime = Request::param('startTime');//提交开始时间
        $endTime = Request::param('endTime');//结交结束时间
        $trxid = Request::param('trxid');//第三方订单号

        $where = [];
        
        //$where = ' 1=1 ';

        if(!empty($phone)){
            $where[] = ['u.phone','like',"%$phone%"];
            //$where .= ' and u.phone like "%'.$phone.'%" ';
        }
        if(!empty($type)){
            $where[] = ['r.payment_type','=',$type];
            //$where .= ' and r.payment_type =  '.$type.' ';
        }
        if(!empty($state)){
            $where[] = ['r.state','=',$state - 1 ];
            //$where .= ' and r.state =  '.$state.'-1 ';
        }
        if(!empty($startTime)){
            $where[] = ['r.addtime','>=',strtotime($startTime)];
            //$where .= ' and r.addtime >=  '.strtotime($startTime).' ';
        }
        if(!empty($endTime)){
            $where[] = ['r.addtime','<=',strtotime($endTime) + 86399];
            //$where .= ' and r.addtime <=  '.strtotime($endTime).'+ 86399 ';
        }
        
        if(!empty($trxid)){
            $strnum = strlen($trxid);
            if($strnum==18){
                $where[] = ['r.payment_type','>=',7];
                $where[] = ['r.payment_type','<=',8];
                $where[] = ['r.action_data->trxid','=',$trxid];
                //$where .= ' and json_extract(`action_data`, "$.trxid")= '.$trxid.' ';
            }
            else{
                //json_extract(`action_data`, '$.>data.id')
                //json_extract(action_data->>'$.data','$.id')
                //select * from pg_recharge where payment_type >=5 and payment_type<=6 and json_extract(action_data->>'$.data','$.id') ='002212023110922595410568704498046259200'
                $where[] = ['r.payment_type','>=',5];
                $where[] = ['r.payment_type','<=',6];
                $where[] = ['r.action_data','like',"%$trxid%"];
                //$where .= ' and json_extract(action_data->>"$.data","$.id")= "'.$trxid.'" ';
            }
            
        }
        $list = Recharge::rechargeList($where,$page,$num);
        $info = [];
        foreach ($list as $item){
            if($item['payment_type'] == 4){
                if(strpos($item['voucher'] ,"img.alicdn.com") !== false){
    //                $listinfo[$key]['commodity']['picture_url'][$k] =  Config::get('app.pomegranate_url').$value['commodity']['picture'][$k];
                    $item['voucher_url']  =  Config::get('app.pomegranate_url').$item['voucher'] ;
                }
                elseif(strpos($item['voucher'] ,"http") !== false){
                    $item['voucher_url']  =  Config::get('app.pomegranate_url').$item['voucher'] ;
                }
                else{
                    $item['voucher_rul']  =  $item['voucher'] ;
                }
//                $item['voucher'] = Config::get('app.pomegranate_url') . $item['voucher'];
            }
            $item['addtime'] = date('Y-m-d H:i',$item['addtime']);
            if($item['audittime'] > 0){
                $item['audittime'] = date('Y-m-d H:i',$item['audittime']);
            }else{
                $item['audittime'] = '';
            }
            $item['trxid'] = '';
            
            if($item['payment_type'] >=7 && $item['payment_type'] <=8){
                $item['action_data'] = json_decode($item['action_data'],true);
            }
            if(($item['payment_type'] >=7 && $item['payment_type'] <=8) && isset($item['action_data']['trxid'])){
                $item['trxid'] = $item['action_data']['trxid'];
            }
            if($item['payment_type'] >=5 && $item['payment_type'] <=6){
                $action_data = json_decode($item['action_data'],true);
                $item['action_data'] = json_decode( $action_data['data'],true);
//                print_r($item['action_data']);exit;
            }
            
            if(($item['payment_type'] >=5 && $item['payment_type'] <=6) && isset($item['action_data']['id'])){
                
                $item['trxid'] = $item['action_data']['id'];
            }
            $info[] = $item;
        }
        return json(['code'=>1,'msg'=>lang(''),'data'=>$info,'count'=>Recharge::rechargeCount($where)]);
    }
    
    //Array ( [created_time] => 1699449712 [data] => {"app_id":"app_83ee6684-5218-4e02-9123-9e8ca2540af8","business_mode":"00","created_time":"20231108212152","description":"","end_time":"20231108212248","expend":{"buyer_logon_id":"653***@qq.com","cashPayAmt":"210.00","sub_open_id":"2088502806643163"},"fee_amt":"0.74","id":"002212023110821215210568317438262050816","order_no":"HF23110821211799902","out_trans_id":"2023110822001443161422309902","party_order_id":"02212311087691226909966","pay_amt":"210.00","pay_channel":"alipay_qr","real_amt":"210.00","share_eq":"Y","status":"succeeded","trans_response_add_info":"{\"fund_bill_list\":[{\"amount\":\"210.00\",\"fund_channel\":\"ALIPAYACCOUNT\"}]}"} [prod_mode] => true [sign] => lMW8ADTPcPCHpqF3KVXbKcnc/SLTKD+paeiCQ31oIopgcezalOvVMh/gqYxwZDHguNRzDOZwSm+vicUQ1tqPo/a0YLPRSDuxIz4ymDZhE5XjjdcexQexqIG2v+L2p07yTLELzN8j2HPUZBEajXluhLYCA189PTutiFkcx3rh+U0= [id] => 002210568317677243461632 [type] => payment.succeeded [app_id] => app_83ee6684-5218-4e02-9123-9e8ca2540af8 [object] => payment ) ;

    //充值记录  excel 导出
    public function indexExcel(){
        $phone = Request::param('phone');//商家手机号码
        $type = Request::param('type');//支付类型 1：银行卡  2：支付宝  3：微信  4：杭州银行自助充值 ，5：汇付天下支付宝扫码   6：汇付天下微信公众号充值
        $state =  Request::param('state'); //状态  0全部   1待审/待支付  2审核通过/充值成功  3审核失败/充值失败
        $startTime = Request::param('startTime');//提交开始时间
        $endTime = Request::param('endTime');//结交结束时间

        $where = [];
        if(!empty($phone)){
            $where[] = ['u.phone','like',"%$phone%"];
        }
        if(!empty($type)){
            $where[] = ['r.payment_type','=',$type];
        }
        if(!empty($state)){
            $where[] = ['r.state','=',$state - 1 ];
        }
        if(!empty($startTime)){
            $where[] = ['r.addtime','>=',strtotime($startTime)];
        }
        if(!empty($endTime)){
            $where[] = ['r.addtime','<=',strtotime($endTime) + 86399];
        }

        $count = Recharge::rechargeCount($where);
        if($count > 3000){
            return json(['code'=>0,'msg'=>lang('单次导出数据不能大于3000条'),'data'=>[],'count'=>'']);
        }
        $list = Recharge::rechargeList($where,1,3000);
        if(empty($list)){
            return json(['code'=>0,'msg'=>lang('未获取到需要导出的数据'),'data'=>[],'count'=>'']);
        }
        $expTableData = [];
        foreach ($list as $item){
            $item['addtime'] = date('Y-m-d H:i',$item['addtime']);
            if($item['payment_type'] == 1) {
                $item['payment_type'] = '银行卡';
            }elseif ($item['payment_type'] == 2){
                $item['payment_type'] = '支付宝';
            }elseif ($item['payment_type'] == 3){
                $item['payment_type'] = '微信';
            }elseif ($item['payment_type'] == 4){
                $item['payment_type'] = '杭州银行';
            }elseif ($item['payment_type'] == 5){
                $item['payment_type'] = '汇付-支付宝';
            }elseif ($item['payment_type'] == 6){
                $item['payment_type'] = '汇付-微信';
            }else{
                $item['payment_type'] = '未知渠道';
            }
            if($item['state'] == 0) {
                $item['state'] = '待支付';
            }elseif ($item['state'] == 1){
                $item['state'] = '充值成功';
            }elseif ($item['state'] == 2){
                $item['state'] = '失败';
            }else{
                $item['state'] = '未知状态';
            }
            $expTableData[] = $item;
        }
        $fileName = date('Y年m月d日H时i分',time());
        $Excel['fileName']=$fileName.'充值记录列表';//or $xlsTitle
        $Excel['cellName']=['A','B','C','D','E','F'];
        $Excel['H'] = ['A'=>28,'B'=>28,'C'=>28,'D'=>28,'E'=>28,'F'=>58];//横向水平宽度
        $Excel['sheetTitle']= '充值记录列表';//大标题，自定义
        $Excel['xlsCell']=[
            ['addtime','交易时间'],
            ['phone','用户手机号'],
            ['amount_money','充值金额'],
            ['payment_type','充值方式'],
            ['state','状态'],
            ['remarks','备注'],
        ];
        self::excelPut($Excel,$expTableData);
    }


    //商家杭州银行充值记录
    public function merchantHzRechargeList(){
        $page = Request::param('page');
        $num = Request::param('num');
        $phone = Request::param('phone');//商家手机号码
        $state = Request::param('state');//状态  0全部   1待审  2审核通过  3审核失败
        $startTime = Request::param('startTime');//提交开始时间
        $endTime = Request::param('endTime');//结交结束时间

        $where = [];
        $where[] = ['r.payment_type','=',4];
        if(!empty($phone)){
            $where[] = ['u.phone','like',"%$phone%"];
        }
        if(!empty($state)){
            $where[] = ['r.state','=',$state - 1 ];
        }
        if(!empty($startTime)){
            $where[] = ['r.addtime','>=',strtotime($startTime)];
        }
        if(!empty($endTime)){
            $where[] = ['r.addtime','<=',strtotime($endTime) + 86399];
        }
        $list = Recharge::rechargeList($where,$page,$num);
        $info = [];
        foreach ($list as $item){
//            $item['voucher'] = Config::get('app.pomegranate_url') . $item['voucher'];
            if(strpos($item['voucher'] ,"img.alicdn.com") !== false){
//                $listinfo[$key]['commodity']['picture_url'][$k] =  Config::get('app.pomegranate_url').$value['commodity']['picture'][$k];
                $item['voucher_url']  =  Config::get('app.pomegranate_url').$item['voucher'] ;
            }
            elseif(strpos($item['voucher'] ,"http") !== false){
                $item['voucher_url']  =  Config::get('app.pomegranate_url').$item['voucher'] ;
            }
            else{
                $item['voucher_rul']  =  $item['voucher'] ;
            }
            $item['addtime'] = date('Y-m-d H:i',$item['addtime']);
            if($item['audittime'] > 0){
                $item['audittime'] = date('Y-m-d H:i',$item['audittime']);
            }else{
                $item['audittime'] = '';
            }
            $info[] = $item;
        }
        return json(['code'=>1,'msg'=>lang(''),'data'=>$info,'count'=>Recharge::rechargeCount($where)]);
    }
    
    public function abnormalRecharge(){
        $sql = "select count(*) as tun,uid,phone,uadd,ma,FROM_UNIXTIME( any_value(n.rtime), '%Y-%m-%d %H:%i:%s' ) as rtime from (
select any_value(r.uid) as uid,any_value(u.phone) as phone,count(json_extract(`action_data`, '$.acct')) as numacct,any_value(json_extract(`action_data`, '$.acct')) as acct,FROM_UNIXTIME( any_value(u.addtime), '%Y-%m-%d %H:%i:%s' ) as uadd,any_value(l.mem_name) as ma,max(r.addtime) as rtime
from pg_recharge as r 
left join pg_user as u on u.uid=r.uid
left join pg_user_member as m on u.uid=m.uid
left join  pg_member_leve as l on l.id=m.m_id 
where r.state=1 and r.payment_type>7 and FROM_UNIXTIME(r.addtime ,'%Y-%m-%d') >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
group by json_extract(`action_data`, '$.acct')
order by any_value(r.addtime) desc ) as n
group by uid having count(uid)>4
order by tun desc";
        $listinfo = AdminSalesmanRule::ruleSql($sql);
        return json(['code'=>1,'msg'=>lang(''),'data'=>$listinfo,'count'=>'']);
    }


    //充值审核
    public function merchantHzRechargeAudit(){
        $adminId = Request::param('a_uid');//后台登录人员id
        $rechargeId = Request::param('rechargeId');//提现记录id
        $amountMoney = Request::param('amountMoney');//修改充值金额
        $state = Request::param('state'); //审核状态  1通过  2不通过
        $remarks = Request::param('remarks');//备注

        $recharge = Recharge::rechargeView(['payment_type'=>4,'id'=>$rechargeId]);
        if(empty($recharge)){
            return json(['code'=>0,'msg'=>lang('充值记录不存在'),'data'=>[],'count'=>'']);
        }
        if($recharge['state'] != 0){
            return json(['code'=>0,'msg'=>lang('记录不在审核状态或已审核'),'data'=>[],'count'=>'']);
        }
        if($state == 1){//审核通过
            if(!empty($amountMoney)){
                $recharge['amount_money'] = $amountMoney;
                $update = ['amount_money'=>$amountMoney,'state' => $state, 'audittime' => time(),];
            }else{
                $update = ['state' => $state, 'audittime' => time(),];
            }

            Recharge::rechargeEdit(['id'=>$rechargeId],$update); //记录修改

            //资金发放
            $UserAmount =  new User_amount();
            $userAmount = $UserAmount->userFind($recharge['uid']);
            $detailed = \detailed($recharge['uid'],$recharge['amount_money'],'recharge_hzpay',$userAmount['balance'],$rechargeId,'','');

            //资金增加
            $UserAmount->auditRecharge($recharge['uid'],bcadd($userAmount['balance'],$recharge['amount_money'],2));
            //资金流水记录
            $Detailed = new Detailed();
            $Detailed->addDetailed($detailed);
        }else{//审核不通过
            $update = [
                'state' => $state,
                'remarks' => $remarks,
                'audittime' => time(),
            ];
            Recharge::rechargeEdit(['id'=>$rechargeId],$update); //记录修改
        }


        //审核操作记录
        $Operation = new Operation();
        $data =[
            'uid' => $adminId,
            'operation' => '自助充值审核',
            'main_table' => 'nh_recharge',
            'mt_id' => $rechargeId,
            'addtime' => time(),
        ];
        $Operation->operationAdd($data);

        return json(['code'=>1,'msg'=>lang(''),'data'=>[],'count'=>'']);

    }




    /**
     * Excel输出
     * @access public
     * @param  array     $Excel Excel相关配置信息
     * @param  array     $expTableData 表格里面的数据
     * @return file
     */
    public static function excelPut($Excel,$expTableData){
        //  ------------- 文件参数 -------------
        $cellName = $Excel['cellName'];
        $xlsCell = $Excel['xlsCell'];
        $cellNum = count($xlsCell);//计算总列数
        $dataNum = count($expTableData);//计算数据总行数
        $spreadsheet = new Spreadsheet();
        $sheet0 = $spreadsheet->getActiveSheet();
        $sheet0->setTitle("杭州集好货");
        //设置表格标题A1
        $sheet0->mergeCells('A1:'.$cellName[$cellNum-1].'1');//表头合并单元格

        // ------------- 表头 -------------
        $sheet0->setCellValue('A1',$Excel['sheetTitle']);

        $sheet0->getStyle('A1')->getFont()->setSize(20);
        $sheet0->getStyle('A1')->getFont()->setName('微软雅黑');
        //设置行高和列宽
        // ------------- 横向水平宽度 -------------
        if(isset($Excel['H'])){
            foreach ($Excel['H'] as $key => $value) {
                $sheet0->getColumnDimension($key)->setWidth($value);
            }
        }

        // ------------- 纵向垂直高度 -------------
        if(isset($Excel['V'])){
            foreach ($Excel['V'] as $key => $value) {
                $sheet0->getRowDimension($key)->setRowHeight($value);
            }
        }

        // ------------- 第二行：表头要加粗和居中，加入颜色 -------------
        $sheet0->getStyle('A1')->applyFromArray(['font' => ['bold' => false],'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
        $setcolor = $sheet0->getStyle("A2:".$cellName[$cellNum-1]."2")->getFill();
        $setcolor->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $colors=['00a000','53a500','3385FF','00a0d0','0C8080','EFE4B0','8db4e2','00b0f0','0fb746'];//设置总颜色
        $selectcolor=$colors[mt_rand(0,count($colors)-1)];//获取随机颜色
        $setcolor->getStartColor()->setRGB($selectcolor);

        // ------------- 根据表格数据设置列名称 -------------

        foreach ($xlsCell as $key => $value) {
            $sheet0->setCellValue($cellName[$key].'2', $value[1])
                ->getStyle($cellName[$key].'2')
                ->applyFromArray(['font' => ['bold' => true],'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
        }

        // ------------- 渲染表中数据内容部分 -------------

        foreach ($expTableData as $keyi => $valuei) {
            foreach ($xlsCell as $keyj => $valuej) {
                $sheet0->getStyle($cellName[$keyj].($keyi+3))->applyFromArray(['alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
                $sheet0->setCellValueExplicit($cellName[$keyj] . ($keyi + 3), $valuei[$valuej[0]], PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet0->getStyle($cellName[$keyj].($keyi+3))->getNumberFormat()->setFormatCode("@");
            }
        }
        // ------------- 设置边框 -------------
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF505050'],
                ],
            ],
        ];

        $sheet0->getStyle('A2:'.$cellName[$cellNum-1].($keyi+3))->applyFromArray($styleArray);

        // 冻结表格
        if(isset($Excel['freeze'])){
            $sheet0->freezePane($Excel['freeze']);
        }
        // ------------- 输出 -------------
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        //header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件
        header("Content-Disposition: attachment;filename=".$Excel['fileName'].".xlsx");//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');//禁止缓存
        //$writer = new Xls($spreadsheet);//输出xls文件
        $writer = new Xlsx($spreadsheet);//输出xlsx文件
        $writer->save('php://output');
        exit;
    }
}